
package DBLayer;
import java.sql.*;
import ModelLayer.Task;
import ModelLayer.PartOrder;
import ModelLayer.TaskDescription;
import ModelLayer.Employee;
import ModelLayer.Machine;
import ModelLayer.ProductDescription;
import java.util.ArrayList;

/**
 * @version 12/22/2011
 * @author Gruppe 4
 */

public class DBTask implements IFDBTask{
    private Connection con;
    private PreparedStatement pStmtSelect;
    
    /**
     * Constructor for class DBMachine
     */
    public DBTask(){
        con = DBConnection.getInstance().getDBcon();
    }
    
    /**
     * Not in use
     */
    @Override
    public ArrayList<Task> getAllTasks(boolean retrieveAssociation){
        return null;
    }
    
    /**
     * Method to find all Task objects in the database with a specific orderId
     * @param orderId int orderId the orderId of the Tasks 
     * @param retrieveAssociation retreiveAssociation boolean retreiveAssociation
     * @return returns an ArrayList<Task>
     */
    @Override
    public ArrayList<Task> findTasksByOrderId(int orderId, boolean retrieveAssociation)
    {
        String wClause = "  where orderId = ";
        
        return findTasksWithPreQuery(orderId, wClause, retrieveAssociation);
    }
    
    /**
     * Method to find all partOrder objects in the database with a specific taskId
     * @param taskId int taskId the taskId of the partOrders 
     * @param retrieveAssociation retreiveAssociation boolean retreiveAssociation
     * @return returns an ArrayList<PartOrder>
     */
    @Override
    public ArrayList<PartOrder> findPartOrdersByTaskId(int taskId, boolean retrieveAssociation)
    {
        String wClause = "  where taskId = ";
        System.out.println("findPartOrdersByTaskId");
        
        return findPartOrdersWithPreQuery(taskId, wClause, retrieveAssociation);
    }
    
   /**
     * Method to find a Task object in the database
     * @param taskId int taskId the taskId of the task
     * @return returns an Object of type Task
     */
    @Override
    public Task findTask(int taskId, boolean retrieveAssociation)
    {   
        String wClause = "  where taskId = ";
        Task taskObj = new Task();
        taskObj = findWithPreQuery(taskId, wClause, retrieveAssociation);
        
        return taskObj;
    }
    
    /**
     * Method to find a Task object in the database
     * @param taskId int taskId the taskId of the Task
     * @return returns an Object of type Task
     */
    @Override
    public Task searchTaskId(int taskId, boolean retrieveAssociation)
    {
        String wClause = "  where taskId = ";
        Task taskObj = new Task();
        taskObj = findWithPreQuery(taskId, wClause, retrieveAssociation);
        
        return taskObj;
    }
    
    /**
     * Method to find a Task object in the database
     * @param startDate String startDate the startDate of the Task
     * @param retrieveAssociation boolean
     * @return returns an Object of type Task
     */
    @Override
    public Task searchTaskDate(String startDate, boolean retrieveAssociation)
    {
        String wClause = "  where startDate = ";
        Task taskObj = new Task();
        taskObj = findWithPreQuery(startDate, wClause, retrieveAssociation);
        
        return taskObj;
    }
    
    /**
     * Method to insert a Task object into the database
     * @param taskObj an Object of the type task
     * @param orderId boolean
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int insertTask(Task taskObj, int orderId) throws Exception
    {
        int rc = -1;
        
        //call to get the next task id
        int nextTaskId = getMaxId();
        nextTaskId = nextTaskId + 1;
        System.out.println("next task id = " +  nextTaskId);
        
        
        
        PreparedStatement pstmt = null;
        
        
        String insertTask = "INSERT INTO Task(taskId, name, disclaimer, startDate, endDate, orderId)"+
                     "VALUES (?,?,?,?,?,?)";
        
        
        System.out.println(insertTask);
        
        
        
            pstmt = con.prepareStatement(insertTask);
            
            pstmt.setInt(1,nextTaskId);
            pstmt.setString(2, taskObj.getName());
            pstmt.setString(3,taskObj.getDisclaimer());
            pstmt.setString(4, taskObj.getStartDate());
            pstmt.setString(5, taskObj.getEndDate());
            pstmt.setInt(6, orderId); 
            
            
            
            
            rc = pstmt.executeUpdate();
        
       
        
        return rc;
    }
    
    /**
     * Method to insert a PartOrder object into the database
     * @param partOrder an Object of the type PartOrder
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int insertPartOrder(PartOrder partOrder) throws Exception
    {
        
        int rc = -1;
        
        int nextPartOrderId = GetMax.getMaxId("Select max(partOrderId) from PartOrder");
        nextPartOrderId = nextPartOrderId + 1;
        System.out.println("next Part Order id = " +  nextPartOrderId);
        
        PreparedStatement pstmt = null;
        
        String insertPartOrder = "INSERT INTO PartOrder(partOrderId, amount, machineId, employeeId, proDescId, taskId)"+
                     "VALUES (?,?,?,?,?,?)";
        
        System.out.println(insertPartOrder);
        
        pstmt = con.prepareStatement(insertPartOrder);
               
        pstmt.setInt(1, nextPartOrderId);
        pstmt.setDouble(2, partOrder.getAmount());
        
        if(partOrder.getMachine() != null)
        {
            pstmt.setInt(3, partOrder.getMachine().getMachineId());
            pstmt.setString(4, null);
            pstmt.setString(5, null);
        }
        if(partOrder.getEmployee() != null)
        {
            pstmt.setString(3, null);
            pstmt.setInt(4, partOrder.getEmployee().getPersonId());
            pstmt.setString(5, null);
        }
        if(partOrder.getProDesc() != null)
        {
            pstmt.setString(3, null);
            pstmt.setString(4, null);
            pstmt.setInt(5, partOrder.getProDesc().getProDescId());
        }
        pstmt.setInt(6, getMaxId());
        
        rc = pstmt.executeUpdate();
        
        return rc;
    }
    
    /**
     * Method to update a Task in the database;
     * @param task an Object of the type Task
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int updateTask(Task task)
    {
        Task taskObj  = task;
        int rc=-1;
        PreparedStatement pstmt = null;
        
        String query="UPDATE Task SET "+
                "taskId = ? "+ 
                "name = ? "+
                "disclaimer = ? "+
                "startDate = ? "+
                "endDate = ? "+
               
                "WHERE taskId = ?";
        
        System.out.println("Update query:" + query);
        try{
                    pstmt = con.prepareStatement(query);
                    
                    pstmt.setInt(1, taskObj.getTaskId());
                    pstmt.setString(2, taskObj.getName());
                    pstmt.setString(3, taskObj.getDisclaimer());
                    pstmt.setString(4, taskObj.getStartDate());
                    pstmt.setString(5, taskObj.getEndDate());
                    
                    rc = pstmt.executeUpdate();
	 	 	pstmt.close();
        }
        
        catch(Exception ex){
	 	 System.out.println("Update exception in Invoice db: "+ex);
	  	}
		return(rc);     
    }
    
    /**
     * Method to delete a Task from the database
     * @param taskId int taskId the id of the task
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int deleteTask(int taskId)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String delete = "delete from Task where taskId = ?";
        System.out.println(delete);
        try
        {
            pstmt = con.prepareStatement(delete);
            pstmt.setInt(1,taskId);
            rc = pstmt.executeUpdate(); 
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            e.getMessage();
        }
        
        return rc;
    }
    
    /**
     * Method to build an object of type Task
     * @param results 
     * @param retreiveAssociation boolean
     * @return Returns a Task object
     */
    private Task buildTask(ResultSet results, boolean retrieveAssociation)
    {
       Task taskObj = new Task();
       
         try{
             
               taskObj.setTaskId(results.getInt("taskId"));
               taskObj.setName(results.getString("name"));
               taskObj.setDisclaimer(results.getString("disclaimer"));
               taskObj.setStartDate(results.getString("startDate"));
               taskObj.setEndDate(results.getString("endDate"));
               
          
         }
         catch(Exception e)
         {
             System.out.println("error building task object");
             System.out.println(e.getMessage());
         }
        
        return taskObj;
        
    }
    
    /**
     * Method to build an object of type PartOrder
     * @param results 
     * @param retreiveAssociation boolean
     * @return Returns a PartOrder object
     */
    private PartOrder buildPartOrder(ResultSet results, boolean retrieveAssociation)
    {
       PartOrder partOrderObj = new PartOrder();
       
         try{
               partOrderObj.setPartOrderId(results.getInt("partOrderId"));
               partOrderObj.setAmount(results.getDouble("amount"));
               partOrderObj.setDepth(results.getString("depth"));
               if(results.getInt("employeeId") != 0)
               {
                    partOrderObj.setEmployee(new Employee(results.getInt("employeeId")));
               }
               
               if(results.getInt("proDescId") != 0)
               {
                    partOrderObj.setProDesc(new ProductDescription(results.getInt("proDescId")));
               }
               
         }
         catch(Exception e)
         {
             System.out.println("error building partorder object");
             System.out.println(e.getMessage());
         }
        
        return partOrderObj;   
    }
    
    /**
     * Method that finds a Task in the database 
     * @param value
     * @param wClause
     * @param retrieveAssociation 
     * @return Task Object
     */
    private Task findWithPreQuery(Object value, String wClause, boolean retrieveAssociation)
        {   
            System.out.println("Find with preCompiled");
            int taskId = 0;
            String startDate = "";
            if(pStmtSelect == null)
            {
               pStmtSelect =  getPreparedStmt(wClause);          
            }
            Task tObj = null;
            ResultSet results;
            
            try
        {
            if(value instanceof Integer)
            {
                taskId = (Integer) value;
                pStmtSelect.setInt(1,taskId);
            }
            if(value instanceof String)
            {
                startDate = (String) value;
                pStmtSelect.setString(1,startDate);
            }
           
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                tObj = buildTask(results, retrieveAssociation);
            }
        } 
        catch(Exception e)
        {
            e.getMessage();
        }
        
        return tObj;
    }
    
    /**
     * Method that finds all Tasks in the database with attribute value 
     * @param value
     * @param wClause
     * @param retrieveAssociation 
     * @return ArrayList<Task> : list
     */
    private ArrayList<Task> findTasksWithPreQuery(Object value, String wClause, boolean retrieveAssociation)
    {
        System.out.println("Find with preCompiled");
        int orderId = 0;
        String orderDate = "";
            
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmt(wClause);          
        }
        ArrayList<Task> list = new ArrayList<Task>();
        ResultSet results;
        try
        {
            if(value instanceof Integer)
            {
                orderId = (Integer) value;
                pStmtSelect.setInt(1,orderId);
            }
            if(value instanceof String)
            {
                orderDate = (String) value;
                pStmtSelect.setString(1,orderDate);
            }
           
            results = pStmtSelect.executeQuery();
            while( results.next() )
            {
                Task taskObj = new Task();
		taskObj = buildTask(results, false);	
                list.add(taskObj);
            }//end while
            pStmtSelect = null;
            System.out.println("Størrelse på liste med tasks: "+ list.size());
            if(retrieveAssociation)
            {   //The supervisor and department is to be build as well
                for(Task taskObj : list)
                {     
                    
                    ArrayList<PartOrder> partObj = findPartOrdersByTaskId(taskObj.getTaskId(), true);
                    System.out.println("PartOrders is selected" + " " + partObj);
                    taskObj.setArrayPartOrders(partObj);  
                 }
            }//end if  
            
        } 
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return list;
    }
    
    /**
     * Method that finds all PartOrders in the database with attribute value 
     * @param value
     * @param wClause
     * @param retrieveAssociation 
     * @return ArrayList<PartOrder> : list
     */
    private ArrayList<PartOrder> findPartOrdersWithPreQuery(Object value, String wClause, boolean retrieveAssociation)
    {
        System.out.println("Find with preCompiled - partOrders");
        int taskId = 0;
        String orderDate = "";
            
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmtPartOrder(wClause);          
        }
        ArrayList<PartOrder> list = new ArrayList<PartOrder>();
        ResultSet results;
        try
        {
            if(value instanceof Integer)
            {
                taskId = (Integer) value;
                pStmtSelect.setInt(1,taskId);
            }
            if(value instanceof String)
            {
                orderDate = (String) value;
                pStmtSelect.setString(1,orderDate);
            }
           
            results = pStmtSelect.executeQuery();
            while( results.next() )
            {
                PartOrder partOrderObj = new PartOrder();
		partOrderObj = buildPartOrder(results, false);	
                list.add(partOrderObj);
            }//end while
            if(retrieveAssociation)
            {   //The supervisor and department is to be build as well
                for(PartOrder partOrderObj : list)
                {     
                    if(partOrderObj.getProDesc() != null)
                    {
                    IFDBProduct dbProductObj = new DBProduct();
                    ProductDescription proDesc = (ProductDescription) partOrderObj.getProDesc();
                    ProductDescription proObj = dbProductObj.findProductDescription(proDesc.getProDescId(), false);
                    System.out.println("ProductDescription is seleceted ");
                    partOrderObj.setProDesc(proObj); 
                    }

                    if(partOrderObj.getEmployee() != null)
                    {
                    IFDBPerson dbPersonObj = new DBPerson();
                    Employee emp = (Employee) partOrderObj.getEmployee();
                    Employee empObj = (Employee) dbPersonObj.findPerson(emp.getPersonId(), false, true);
                    System.out.println("Employee is seleceted ");
                    partOrderObj.setEmployee(empObj);
                    }
                    if(partOrderObj.getMachine() != null)
                    {                 
                    }
                    // build associations
                 }
            }//end if      
        } 
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return list;
    }
    
    /**
     * Method to create a prepared statement for use with type Task
     * @param wClause String wClause
     * @return returns a PreparedStatment
     */
    private PreparedStatement getPreparedStmt(String wClause)
        {
           try{ 
            String query = "Select * FROM Task " +
                           wClause + " ?";
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            
           }
           catch(Exception e)
           {
                e.getMessage();
            }
           return pStmtSelect;
        }
    
    /**
     * Method to create a prepared statement for use with type Order
     * @param wClause String wClause
     * @return returns a PreparedStatment
     */
    private PreparedStatement getPreparedStmtPartOrder(String wClause)
        {
           try{ 
            String query = "Select * FROM PartOrder " +
                           wClause + " ?";
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            
           }
           catch(Exception e)
           {
                e.getMessage();
            }
           return pStmtSelect;
        }
    
    /**
     * Method to get the highest taskId that excist in the database
     * @return returns the highest taskId
     */
    public int getMaxId()
    {
        int maxTaskId = GetMax.getMaxId("Select max(taskId) from Task");
        return maxTaskId;
    }
    
}
